﻿/*
 数据库 操作类
 */
namespace Core.Helper
{
    using System;
    using System.Data.SqlClient;
    using System.Data;
    public class DbHelper
    {
        #region GET DataRow
        /// <summary>
        /// 存储过程获取数据集
        /// </summary>
        public static DataRow ExecuteDataRow(string sqlSpName, params object[] parameters)
        {
            try
            {
                return SqlHelper.ExecuteDataset(DBConnString.ConnString, sqlSpName, parameters).Tables[0].Rows[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// SQL获取数据集
        /// </summary>
        public static DataRow ExecuteDataRow(string commandText)
        {
            try
            {
                return SqlHelper.ExecuteDataset(DBConnString.ConnString, CommandType.Text, commandText).Tables[0].Rows[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 存储过程或带参数SQL获取数据集
        /// </summary>
        public static DataRow ExecuteDataRow(CommandType ctype, string command, params SqlParameter[] parameters)
        {
            try
            {
                return SqlHelper.ExecuteDataset(DBConnString.ConnString, ctype, command, parameters).Tables[0].Rows[0];
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region GET DataSet
        /// <summary>
        /// 存储过程获取数据集
        /// </summary>
        public static DataSet ExecuteDataSet(string sqlSpName, params object[] parameters)
        {
            return SqlHelper.ExecuteDataset(DBConnString.ConnString, sqlSpName, parameters);
        }

        /// <summary>
        /// SQL获取数据集
        /// </summary>
        public static DataSet ExecuteDataSet(string commandText)
        {
            return SqlHelper.ExecuteDataset(DBConnString.ConnString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 存储过程或带参数SQL获取数据集
        /// </summary>
        public static DataSet ExecuteDataSet(CommandType ctype, string command, params SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteDataset(DBConnString.ConnString, ctype, command, parameters);
        }

        #endregion

        #region GET Reader
        /// <summary>
        /// 存储过程获取Reader
        /// </summary>
        public static SqlDataReader ExecuteDataReader(string sqlSpName, params SqlParameter[] Parameters)
        {
            return SqlHelper.ExecuteReader(DBConnString.ConnString, CommandType.StoredProcedure, sqlSpName, Parameters);
        }

        /// <summary>
        /// SQL获取Reader
        /// </summary>
        public static SqlDataReader ExecuteDataReader(string commandText)
        {
            return SqlHelper.ExecuteReader(DBConnString.ConnString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 存储过程或SQL获取Reader
        /// </summary>
        public static SqlDataReader ExecuteDataReader(CommandType ctype, string command, params SqlParameter[] Parameters)
        {
            return SqlHelper.ExecuteReader(DBConnString.ConnString, ctype, command, Parameters);
        }
        #endregion

        #region GET EffectCount
        /// <summary>
        /// 存储过程获取影响行数
        /// </summary>
        public static int ExecuteNonQuery(string sqlSpName, params SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteNonQuery(DBConnString.ConnString, sqlSpName, parameters);
        }
        /// <summary>
        /// SQL获取影响行数
        /// </summary>
        public static int ExecuteNonQuery(string commandText)
        {
            return SqlHelper.ExecuteNonQuery(DBConnString.ConnString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 存储过程或带参数SQL获取影响行数
        /// </summary>
        public static int ExecuteNonQuery(CommandType ctype, string command, params SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteNonQuery(DBConnString.ConnString, ctype, command, parameters);
        }
        #endregion

        #region GET Scalar
        /// <summary>
        /// 存储过程获取Scalar
        /// </summary>
        public static object ExecuteScalar(string sqlSpName, params SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteScalar(DBConnString.ConnString, sqlSpName, parameters);
        }

        /// <summary>
        /// SQL获取Scalar
        /// </summary>
        public static object ExecuteScalar(string commandText)
        {
            return SqlHelper.ExecuteScalar(DBConnString.ConnString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 存储过程或带参数SQL获取Scalar
        /// </summary>
        public static object ExecuteScalar(CommandType ctype, string command, params SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteScalar(DBConnString.ConnString, ctype, command, parameters);
        }

        #endregion

        #region Get DataSet Paged
        /// <summary>
        /// 带返回行数(使用presql生成临时表 然后将临时表删除）
        /// </summary>
        public static DataTable PageData(string sortfield, int pagesize, int pageindex, string tablename, string querySqlText, ref int recordcount, Boolean isTmpTb = false, string preSql = "")
        {
            if (pageindex > 0) { pageindex = pageindex - 1; }
            var sb = new System.Text.StringBuilder();
            if (isTmpTb)
            {
                if (querySqlText == "")
                { querySqlText = " 1=1 "; }

                sb.Append(preSql + ";");
                sb.Append(String.Format(" WITH Data_DataSet AS ( SELECT ROW_NUMBER() OVER (ORDER BY " + sortfield + " ) AS Row, *  FROM " + tablename + " Where  " + querySqlText + " ) SELECT * FROM Data_DataSet  WHERE Row between {0}* {1}+1  and ({0}+1)*{1} select ISNULL(count(1),0) from " + tablename + " Where " + querySqlText, pageindex, pagesize));
                sb.Append(" drop table " + tablename);

                try
                {
                    DataSet dsResult = ExecuteDataSet(sb.ToString());

                    recordcount = Convert.ToInt32(dsResult.Tables[1].Rows[0][0].ToString());

                    return dsResult.Tables[0];
                }
                catch
                {
                    recordcount = 0;
                    return null;
                }
            }
            else { return PageData(sortfield, pagesize, pageindex, tablename, querySqlText, ref recordcount); }
        }
        /// <summary>
        /// 单个表分页获取数据集
        /// </summary>
        public static DataSet PageData(string sortfield, int pagesize, int pageindex, string tablename, string querySqlText)
        {
            if (querySqlText == "")
            { querySqlText = " 1=1 "; }
            if (pageindex > 0) { pageindex = pageindex - 1; }
            return ExecuteDataSet(String.Format("WITH Data_DataSet AS ( SELECT ROW_NUMBER() OVER (ORDER BY " + sortfield + " ) AS Row, *  FROM " + tablename + " Where  " + querySqlText + " ) SELECT * FROM Data_DataSet  WHERE Row between {0}* {1}+1  and ({0}+1)*{1}", pageindex, pagesize));
        }

        /// <summary>
        /// 带返回行数
        /// </summary>
        public static DataTable PageData(string sortfield, int pagesize, int pageindex, string tablename, string querySqlText, ref int recordcount)
        {
            if (querySqlText == "")
            { querySqlText = " 1=1 "; }
            if (pageindex > 0) { pageindex = pageindex - 1; }
            try
            {
                DataSet dsResult = ExecuteDataSet(String.Format("WITH Data_DataSet AS ( SELECT ROW_NUMBER() OVER (ORDER BY " + sortfield + " ) AS Row, *  FROM " + tablename + " Where  " + querySqlText + " ) SELECT * FROM Data_DataSet  WHERE Row between {0}* {1}+1  and ({0}+1)*{1} select ISNULL(count(1),0) from " + tablename + " Where " + querySqlText, pageindex, pagesize));

                recordcount = Convert.ToInt32(dsResult.Tables[1].Rows[0][0].ToString());

                return dsResult.Tables[0];
            }
            catch (Exception e)
            {
                recordcount = 0;
                return null;
            }
        }
        /// <summary>
        /// 组合SQL分页获取数据集
        /// </summary>
        public static DataSet PageData(string sortfield, int pagesize, int pageindex, string sqlText)
        {
            if (pageindex > 0) { pageindex = pageindex - 1; }
            return ExecuteDataSet(String.Format("WITH Data_DataSet AS ( SELECT ROW_NUMBER() OVER (ORDER BY " + sortfield + ") AS Row, *  FROM ( " + sqlText + " ) aa ) SELECT * FROM Data_DataSet WHERE Row between {0}* {1}+1  and ({0}+1)*{1}", pageindex, pagesize));
        }

        /// <summary>
        /// 组合SQL带返回行数
        /// </summary>
        public static DataTable PageData(string sortfield, int pagesize, int pageindex, string sqlText, ref int recordcount)
        {
            if (pageindex > 0) { pageindex = pageindex - 1; }
            try
            {
                DataSet dsResult = ExecuteDataSet(String.Format("WITH Data_DataSet AS ( SELECT ROW_NUMBER() OVER (ORDER BY " + sortfield + ") AS Row, *  FROM ( " + sqlText + " ) aa ) SELECT * FROM Data_DataSet WHERE Row between {0}* {1}+1  and ({0}+1)*{1} select ISNULL(count(1),0) from (" + sqlText + ") aa", pageindex, pagesize));

                recordcount = Convert.ToInt32(dsResult.Tables[1].Rows[0][0].ToString());

                return dsResult.Tables[0];
            }
            catch (Exception e)
            {
                recordcount = 0;
                return null;
            }
        }
        #endregion
    }
}
